import json
from datetime import datetime, timedelta

import redis
from flask import Blueprint, request, redirect, jsonify
from flask_jwt_extended import get_jwt_identity, verify_jwt_in_request
from sqlalchemy import text, func, and_

from api.word2api import getWordBaseDetailById, getWordDetailById
from models import db, User, StudyLog, Word, get_redis_connection

user_api = Blueprint('user_api', __name__)


@user_api.before_request
def before_request():
    verify_jwt_in_request()


@user_api.route('/')
def index():
    return 'user'


@user_api.route('/studyinfo', methods=['POST'])
def study_info():
    current_user_id = get_jwt_identity()
    learned_words_count = StudyLog.query.filter_by(user_id=current_user_id).count()
    all_words_count = Word.query.count()
    unlearned_words_count = all_words_count - learned_words_count
    review_words_count = StudyLog.query.filter(
        StudyLog.user_id == current_user_id,
        StudyLog.next_study_time <= datetime.utcnow()
    ).count()

    # 近七天学习情况
    start_date = datetime.utcnow().date() - timedelta(days=6)
    # 生成近七天的日期列表
    date_list = [start_date + timedelta(days=x) for x in range(7)]

    # 从StudyLog中筛选过去七天的数据，按照日期分组并计数
    study_data = db.session.query(
        func.date(StudyLog.start_study_time).label('date'),
        func.count(StudyLog.word_id).label('word_count')
    ).filter(
        and_(
            func.date(StudyLog.start_study_time) >= start_date,
            func.date(StudyLog.start_study_time) <= datetime.utcnow().date(),
            StudyLog.user_id == current_user_id
        )
    ).group_by('date').all()

    # 将查询结果转换为字典，便于查找和访问
    study_dict = {record.date: record.word_count for record in study_data}

    # 构建最终结果列表，确保包含所有日期
    result = []
    for date in date_list:
        # 如果某个日期在查询结果中不存在，则添加一个word_count为0的记录
        word_count = study_dict.get(date, 0)
        result.append({'date': date, 'word_count': word_count})

    info = {
        'learned_word_num': learned_words_count,
        '2learn_word_num': unlearned_words_count,
        '2review_word_num': review_words_count,
        'total_word_num': all_words_count,
        'days_study': result
    }
    return info


@user_api.route('/baseinfo', methods=['POST'])
def base_info():
    current_user_id = get_jwt_identity()
    user = User.query.get(current_user_id)
    info = {
        'nickname': user.username,
        'avatar': user.avatar,
        'schedule': user.schedule
    }
    return info


@user_api.route('/gostudy', methods=['POST', "GET"])
def go_study():
    # 随机返回20个该用户未学单词
    current_user_id = get_jwt_identity()
    user = User.query.get(current_user_id)
    redis_client = get_redis_connection()
    if redis_client.exists(f'study:{current_user_id}'):
        the_first_one_id = redis_client.lindex(f'study:{current_user_id}', 0)
        the_first_one = redis_client.hgetall(f'study:{current_user_id}:{the_first_one_id}')
        data_ = {k: json.loads(v) for k, v in the_first_one.items()}
        data_['left_study'] = redis_client.llen(f'study:{current_user_id}')
        return data_
    # 获取用户未学单词
    result = db.session.execute(text("""
    SELECT `id` FROM word WHERE NOT EXISTS(
        SELECT * FROM study_log where study_log.word_id = word.id and study_log.user_id = :user_id
    )
    """), params={'user_id': current_user_id}).fetchall()

    result = [row[0] for row in result]
    if result:
        id_data = result[:int(user.schedule)] if len(result) > int(user.schedule) else result
        for wid in id_data:
            item = getWordDetailById(wid)
            redis_client.rpush(f'study:{current_user_id}', wid)
            mapping = {
                'id': item['id'],
                'word': item['word'],
                'language': item['language'],
                'sentences': item['sentences'],
                'root_word': item['root_word'],
                'root_meaning': item['root_meaning'],
                'meanings': item['meanings'],
                'collocations': item['collocations'],
                'relative_words': item['relative_words'],
                'right_option': item['right_option'],
                'options': item['options'],
                'similar_options': item['similar_options'],
                'times': 0,
                'total_study': len(id_data),
            }
            mapping = {k: json.dumps(v) for k, v in mapping.items()}
            redis_client.hset(f'study:{current_user_id}:{wid}', mapping=mapping)
        the_first_one_id = redis_client.lindex(f'study:{current_user_id}', 0)
        the_first_one = redis_client.hgetall(f'study:{current_user_id}:{the_first_one_id}')
        data_ = {k: json.loads(v) for k, v in the_first_one.items()}
        data_['left_study'] = redis_client.llen(f'study:{current_user_id}')
        return data_
    return jsonify({'msg': '暂无待学习的单词', 'code': 401})


@user_api.route('/goreview', methods=['POST', 'GET'])
def go_review():
    # 随机返回20个用户复习单词
    current_user_id = get_jwt_identity()
    user = User.query.get(current_user_id)
    redis_client = get_redis_connection()
    if redis_client.exists(f'review:{current_user_id}'):
        the_first_one_id = redis_client.lindex(f'review:{current_user_id}', 0)
        the_first_one = redis_client.hgetall(f'review:{current_user_id}:{the_first_one_id}')
        data_ = {k: json.loads(v) for k, v in the_first_one.items()}
        data_['left_review'] = redis_client.llen(f'review:{current_user_id}')
        return data_
    # 获取用户已学单词
    sql = text("""
        SELECT word_id FROM study_log WHERE user_id = :user_id and next_study_time < :date_now
    """)
    param = {
        'user_id': current_user_id,
        'date_now': datetime.utcnow()
    }
    result = db.session.execute(sql, params=param).fetchall()

    result = [row[0] for row in result]
    if result:
        id_data = result[:int(user.schedule)] if len(result) > int(user.schedule) else result
        for wid in id_data:
            item = getWordDetailById(wid)
            redis_client.rpush(f'review:{current_user_id}', wid)
            mapping = {
                'id': item['id'],
                'word': item['word'],
                'language': item['language'],
                'sentences': item['sentences'],
                'root_word': item['root_word'],
                'root_meaning': item['root_meaning'],
                'meanings': item['meanings'],
                'collocations': item['collocations'],
                'relative_words': item['relative_words'],
                'right_option': item['right_option'],
                'options': item['options'],
                'similar_options': item['similar_options'],
                'times': 1,
                'total_review': len(id_data),
            }
            mapping = {k: json.dumps(v) for k, v in mapping.items()}
            redis_client.hset(f'review:{current_user_id}:{wid}', mapping=mapping)
        the_first_one_id = redis_client.lindex(f'review:{current_user_id}', 0)
        the_first_one = redis_client.hgetall(f'review:{current_user_id}:{the_first_one_id}')
        data_ = {k: json.loads(v) for k, v in the_first_one.items()}
        data_['left_review'] = redis_client.llen(f'review:{current_user_id}')
        return data_
    return jsonify({'msg': '暂无待复习的单词', 'code': 401})


@user_api.route('/schedule', methods=['POST'])
def changeSchedule():
    current_user_id = get_jwt_identity()
    user = User.query.get(current_user_id)
    data = request.get_json()
    schedule = int(data.get('schedule'))
    if schedule in [10, 15, 20]:
        user.schedule = schedule
        db.session.commit()
        return {'status': 'success'}
    return {'status': 'error'}
